package nbcp.myoql.code.generator.db.sql

import nbcp.base.extend.Important
import nbcp.base.extend.basicSame
import nbcp.myoql.code.generator.db.mysql.model.TableColumnMetaData
import nbcp.myoql.code.generator.db.mysql.model.TableIndexMetaData
import nbcp.myoql.code.generator.db.mysql.model.TableMetaData
import nbcp.myoql.db.db
import nbcp.myoql.db.sql.enums.DbType
import org.slf4j.LoggerFactory

class MysqlImpl :ISqlMetadata {
    companion object {
        private val logger = LoggerFactory.getLogger(javaClass.declaringClass)
    }

    override fun getTables():  List<TableMetaData> {
        return db.sql.rawQuery(
            """
SELECT 
table_name as `tableName`,
table_comment as `tableComment`
FROM INFORMATION_SCHEMA.TABLES
where table_schema = :db 
order by table_name
"""
        )
            .apply {
                this.sqlParameter.values.put("db", this.catalog)
            }
            .toList(TableMetaData::class.java)
    }

    override fun getColumns():  List<TableColumnMetaData> {
        return db.sql.rawQuery(
            """
SELECT 
    table_name as `tableName` , 
    column_name as `columnName`, 
    data_type as `columnSqlType` ,
    character_maximum_length AS `charLength`,
    column_comment as `columnComment` ,  
    if( column_key  = 'PRI' , 1 , 0 ) AS `isPrimary`,
    if( column_key  = 'UNI' , 1 , 0 ) AS `isUnique`,
    if( extra  = 'auto_increment' , 1 , 0 ) AS `isAutoIncrement`,
    if( extra  = 'VIRTUAL GENERATED' , 1 , 0 ) AS `isVirtual`
FROM INFORMATION_SCHEMA.COLUMNS
where table_schema = :db  
order by 
    table_name , 
    CASE COLUMN_NAME 
        WHEN 'id' THEN -10 
        when 'name' then -9  
        when 'code' then -8
        ELSE  char_length(COLUMN_NAME) - char_length(replace(COLUMN_NAME,'_','')) + 1
    END ASC,
    CHAR_LENGTH(COLUMN_NAME) ASC 
"""
        )
            .apply {
                this.sqlParameter.values.put("db", this.catalog)
            }
            .toList(TableColumnMetaData::class.java)
    }

    override fun getIndexes(): List<TableIndexMetaData> {
        return db.sql.rawQuery(
            """
SELECT 
    table_name as `tableName` ,
    index_name as `indexName`,
    seq_in_index as `seqInIndex`,
    column_name as `columnName`
FROM INFORMATION_SCHEMA.STATISTICS
where table_schema = :db AND non_unique = 0 AND INDEX_name != 'PRIMARY' 
ORDER BY TABLE_NAME , index_name , seq_in_index
"""
        )
            .apply {
                this.sqlParameter.values.put("db", this.catalog)
            }
            .toList(TableIndexMetaData::class.java)
    }


    override fun getDbType(dataType: String): DbType {
        if (dataType basicSame "varchar"
            || dataType basicSame "char"
            || dataType basicSame "nvarchar"
            || dataType basicSame "nchar"
        ) {
            return DbType.STRING
        } else if (dataType basicSame "text"
            || dataType basicSame "mediumtext"
            || dataType basicSame "longtext"
        ) {
            return DbType.TEXT
        } else if (dataType basicSame "enum") {
            return DbType.ENUM
        } else if (dataType basicSame "json") {
            return DbType.JSON
        } else if (dataType basicSame "int") {
            return DbType.INT
        } else if (dataType basicSame "bit") {
            return DbType.BOOLEAN
        } else if (dataType basicSame "datetime" ||
            dataType basicSame "timestamp"
        ) {
            return DbType.DATE_TIME
        } else if (dataType basicSame "date") {
            return DbType.DATE
        } else if (dataType basicSame "float") {
            return DbType.FLOAT
        } else if (dataType basicSame "double") {
            return DbType.DOUBLE
        } else if (dataType basicSame "long") {
            return DbType.LONG
        } else if (dataType basicSame "tinyint") {
            return DbType.BYTE
        } else if (dataType basicSame "bigint") {
            return DbType.LONG
        } else if (dataType basicSame "decimal") {
            logger.Important("warning sql data type: ${dataType}")
            return DbType.DOUBLE
        }

        return DbType.TEXT
    }
}